﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Activity.Data;
using Activity.Model;
using System.Linq.Expressions;

namespace Activity.API
{
    public class TimeAnalysisReportHandler
    {
        /// <summary>
        /// 获取9个分部
        /// </summary>
        /// <param name="EndDate1"></param>
        /// <param name="EndDate2"></param>
        /// <param name="EndDate3"></param>
        /// <returns></returns>
        public IList<TimeAnalysisReportModel.ListModel> GetReprot(DateTime EndDate1, DateTime EndDate2, DateTime EndDate3,int ShopType=0)
        {
            try
            {
                var time1 = $"{EndDate1:yyyy-MM-dd}";
                var time2 = $"{EndDate2:yyyy-MM-dd}";
                var time3 = $"{EndDate3:yyyy-MM-dd}";
                var sql =
                    $@"exec Time_Analysis_Report 'KM','{time1}','{EndDate1:yyyy-MM-dd HH:00:00}','{time2}','{
                            EndDate2
                        :yyyy-MM-dd HH:00:00}','{time3}','{EndDate3:yyyy-MM-dd HH:00:00}',{ShopType}";
                var db = new HKERPEntities();
                var database = db.Database;
                database.CommandTimeout = 0;
                var group = database.SqlQuery<TimeAnalysisReportModel.CoreModel>(sql)
                    .GroupBy(d => new { d.OperationAreaID, d.OperationAreaName });
                var list = group.SelectMany(d => d).Select(d => new TimeAnalysisReportModel.NeceModel
                {
                    CompanyID = d.CompanyID,
                    OperationAreaID = d.OperationAreaID,
                    OperationAreaName = d.OperationAreaName,
                    SaleDate = d.SaleDate,
                    SaleAmount = d.SaleAmount,
                    SaleAmount2 = d.SaleAmount2,
                    SaleAmount3 = d.SaleAmount3,
                    SaleQty = d.SaleQty,
                    SaleQty3 = d.SaleQty3,
                    SaleCount = d.SaleCount,
                    SaleCount2 = d.SaleCount2,
                    SaleCount3 = d.SaleCount3,
                    KpiQty = d.KpiQty,
                    KpiQty2 = d.KpiQty2,
                    SaleKdj = d.SaleKdj,
                    Closing = d.Closing * 100,
                    SalelDv = d.SalelDv,
                    Discount = d.Discount,
                    SaleJdj = d.SaleJdj,
                    ShopCount = d.ShopCount,
                    Price=d.Price,
                }).ToList();
                foreach (var item in group)
                {
                    var date1 = item.FirstOrDefault(d => d.SaleDate == time1);
                    var date2 = item.FirstOrDefault(d => d.SaleDate == time2);
                    var date3 = item.FirstOrDefault(d => d.SaleDate == time3);
                    if (date1 == null)
                    {
                        var newItem = new TimeAnalysisReportModel.NeceModel
                        {
                            OperationAreaID = item.Key.OperationAreaID,
                            OperationAreaName = item.Key.OperationAreaName,
                            SaleDate = time1,
                            Week = $"星期{TimeTrackingReportHandler.ReturnWeek(Convert.ToDateTime(time1))}"
                        };
                        list.Add(newItem);
                    }
                    if (date2 == null)
                    {
                        var newItem = new TimeAnalysisReportModel.NeceModel
                        {
                            OperationAreaID = item.Key.OperationAreaID,
                            OperationAreaName = item.Key.OperationAreaName,
                            SaleDate = time2,
                            Week = $"星期{TimeTrackingReportHandler.ReturnWeek(Convert.ToDateTime(time2))}"
                        };
                        list.Add(newItem);
                    }
                    if (date3 == null)
                    {
                        var newItem = new TimeAnalysisReportModel.NeceModel
                        {
                            OperationAreaID = item.Key.OperationAreaID,
                            OperationAreaName = item.Key.OperationAreaName,
                            SaleDate = time3,
                            Week = $"星期{TimeTrackingReportHandler.ReturnWeek(Convert.ToDateTime(time3))}"
                        };
                        list.Add(newItem);
                    }
                }
                //var totalDate1 = list.Where(d => d.SaleDate == time1);
                //var totalItem1 = new TimeAnalysisReportModel.NeceModel
                //{
                //    OperationAreaName = "总计",
                //    OperationAreaID = "KM9999999999999999999",
                //    SaleDate = time1,
                //    Week = $"星期{TimeTrackingReportHandler.ReturnWeek(Convert.ToDateTime(time1))}",
                //    ShopCount = totalDate1.Sum(d => d.ShopCount),
                //    SaleAmount = totalDate1.Sum(d => d.SaleAmount),
                //    SaleAmount2 = totalDate1.Sum(d => d.SaleAmount2),
                //    SaleAmount3 = totalDate1.Sum(d => d.SaleAmount3),
                //    SaleQty = totalDate1.Sum(d => d.SaleQty),
                //    SaleQty3 = totalDate1.Sum(d => d.SaleQty3),
                //    SaleCount = totalDate1.Sum(d => d.SaleCount),
                //    SaleCount2 = totalDate1.Sum(d => d.SaleCount2),
                //    SaleCount3 = totalDate1.Sum(d => d.SaleCount3),
                //    KpiQty = totalDate1.Sum(d => d.KpiQty),
                //    KpiQty2 = totalDate1.Sum(d => d.KpiQty2),
                //    Price= totalDate1.Sum(d=>d.Price),
                //    //SaleKdj = totalDate1.Sum(d => d.SaleKdj),
                //    //Closing = count == 0 ? 0 : totalDate1.Sum(d => d.Closing) / count,
                //    //SalelDv = count == 0 ? 0 : totalDate1.Sum(d => d.SalelDv) / count,
                //    //Discount = totalDate1.Sum(d => d.Discount),
                //    //SaleJdj = totalDate1.Sum(d => d.SaleJdj),
                //};
                //totalItem1.SaleKdj = totalItem1?.SaleCount == 0 ? 0 : totalItem1?.SaleAmount / (decimal?)totalItem1.SaleCount;
                //totalItem1.Closing = totalItem1?.KpiQty2 == 0 ? 0 : totalItem1?.SaleCount2 * 100 / (decimal?)totalItem1.KpiQty2;
                //totalItem1.SalelDv = totalItem1?.SaleCount3 == 0 ? 0 : totalItem1?.SaleQty3 / (decimal?)totalItem1.SaleCount3;
                //totalItem1.Discount = totalItem1?.Price == 0 ? 0 : totalItem1?.SaleAmount2 / (decimal?)totalItem1.Price;
                //totalItem1.SaleJdj = totalItem1?.SaleQty3 == 0 ? 0 : totalItem1?.SaleAmount3 / (decimal?)totalItem1.SaleQty3;
                //var totalDate2 = list.Where(d => d.SaleDate == time2);
                //var totalItem2 = new TimeAnalysisReportModel.NeceModel
                //{
                //    OperationAreaName = "总计",
                //    OperationAreaID = "KM9999999999999999999",
                //    SaleDate = time2,
                //    Week = $"星期{TimeTrackingReportHandler.ReturnWeek(Convert.ToDateTime(time2))}",
                //    ShopCount = totalDate2.Sum(d => d.ShopCount),
                //    SaleAmount = totalDate2.Sum(d => d.SaleAmount),
                //    SaleAmount2 = totalDate2.Sum(d => d.SaleAmount2),
                //    SaleAmount3 = totalDate2.Sum(d => d.SaleAmount3),
                //    SaleQty = totalDate2.Sum(d => d.SaleQty),
                //    SaleQty3 = totalDate2.Sum(d => d.SaleQty3),
                //    SaleCount = totalDate2.Sum(d => d.SaleCount),
                //    SaleCount2 = totalDate2.Sum(d => d.SaleCount2),
                //    SaleCount3 = totalDate2.Sum(d => d.SaleCount3),
                //    KpiQty = totalDate2.Sum(d => d.KpiQty),
                //    KpiQty2 = totalDate2.Sum(d => d.KpiQty2),
                //    Price = totalDate2.Sum(d => d.Price),
                //    //SaleKdj = totalDate2.Sum(d => d.SaleKdj),
                //    //Closing = count == 0 ? 0 : totalDate2.Sum(d => d.Closing) / count,
                //    //SalelDv = count == 0 ? 0 : totalDate2.Sum(d => d.SalelDv) / count,
                //    //Discount = totalDate2.Sum(d => d.Discount),
                //    //SaleJdj = totalDate2.Sum(d => d.SaleJdj),
                //};
                //totalItem2.SaleKdj = totalItem2?.SaleCount == 0 ? 0 : totalItem2?.SaleAmount / (decimal?)totalItem2.SaleCount;
                //totalItem2.Closing = totalItem2?.KpiQty2 == 0 ? 0 : totalItem2?.SaleCount2 * 100 / (decimal?)totalItem2.KpiQty2;
                //totalItem2.SalelDv = totalItem2?.SaleCount3 == 0 ? 0 : totalItem2?.SaleQty3 / (decimal?)totalItem2.SaleCount3;
                //totalItem2.Discount = totalItem2?.Price == 0 ? 0 : totalItem2?.SaleAmount2 / (decimal?)totalItem2.Price;
                //totalItem2.SaleJdj = totalItem2?.SaleQty3 == 0 ? 0 : totalItem2?.SaleAmount3 / (decimal?)totalItem2.SaleQty3;
                //var totalDate3 = list.Where(d => d.SaleDate == time3);
                //var totalItem3 = new TimeAnalysisReportModel.NeceModel
                //{
                //    OperationAreaName = "总计",
                //    OperationAreaID = "KM9999999999999999999",
                //    SaleDate = time3,
                //    Week = $"星期{TimeTrackingReportHandler.ReturnWeek(Convert.ToDateTime(time3))}",
                //    ShopCount = totalDate3.Sum(d => d.ShopCount),
                //    SaleAmount = totalDate3.Sum(d => d.SaleAmount),
                //    SaleAmount2 = totalDate3.Sum(d => d.SaleAmount2),
                //    SaleAmount3 = totalDate3.Sum(d => d.SaleAmount3),
                //    SaleQty = totalDate3.Sum(d => d.SaleQty),
                //    SaleQty3 = totalDate3.Sum(d => d.SaleQty3),
                //    SaleCount = totalDate3.Sum(d => d.SaleCount),
                //    SaleCount2 = totalDate3.Sum(d => d.SaleCount2),
                //    SaleCount3 = totalDate3.Sum(d => d.SaleCount3),
                //    KpiQty = totalDate3.Sum(d => d.KpiQty),
                //    KpiQty2 = totalDate3.Sum(d => d.KpiQty2),
                //    Price = totalDate3.Sum(d => d.Price),
                //    //SaleKdj = totalDate3.Sum(d => d.SaleKdj),
                //    //Closing = count == 0 ? 0 : totalDate3.Sum(d => d.Closing) / count,
                //    //SalelDv = count == 0 ? 0 : totalDate3.Sum(d => d.SalelDv) / count,
                //    //Discount = totalDate3.Sum(d => d.Discount),
                //    //SaleJdj = totalDate3.Sum(d => d.SaleJdj),
                //};
                //totalItem3.SaleKdj = totalItem3?.SaleCount == 0 ? 0 : totalItem3?.SaleAmount / (decimal?)totalItem3.SaleCount;
                //totalItem3.Closing = totalItem3?.KpiQty2 == 0 ? 0 : totalItem3?.SaleCount2 * 100 / (decimal?)totalItem3.KpiQty2;
                //totalItem3.SalelDv = totalItem3?.SaleCount3 == 0 ? 0 : totalItem3?.SaleQty3 / (decimal?)totalItem3.SaleCount3;
                //totalItem3.Discount = totalItem3?.Price == 0 ? 0 : totalItem3?.SaleAmount2 / (decimal?)totalItem3.Price;
                //totalItem3.SaleJdj = totalItem3?.SaleQty3 == 0 ? 0 : totalItem3?.SaleAmount3 / (decimal?)totalItem3.SaleQty3;
                //list.Add(totalItem1);
                //list.Add(totalItem2);
                //list.Add(totalItem3);
                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda_date1 = d => d.SaleDate == time1;
                list.Add(TranFrom(list.AsQueryable(), time1, "总计", "KM9999999999999999999", whereLambda_date1));
                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda_date2 = d => d.SaleDate == time2;
                list.Add(TranFrom(list.AsQueryable(), time2, "总计", "KM9999999999999999999", whereLambda_date2));
                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda_date3 = d => d.SaleDate == time3;
                list.Add(TranFrom(list.AsQueryable(), time3, "总计", "KM9999999999999999999", whereLambda_date3));
                var newGroup = list.OrderBy(d => d.OperationAreaID).ThenBy(d => d.SaleDate).GroupBy(d => new { d.OperationAreaID, d.OperationAreaName });
                foreach (var item in newGroup)
                {
                    var today = item.FirstOrDefault(d => d.SaleDate == time1);
                    var yestoday = item.FirstOrDefault(d => d.SaleDate == time2);
                    var lastweek = item.FirstOrDefault(d => d.SaleDate == time3);
                    var neighborhoodCom_SaleAmount = (yestoday?.SaleAmount == 0 ? 0 : (today?.SaleAmount / yestoday?.SaleAmount) - 1) * 100;
                    var neighborhoodCom_SaleQty = (yestoday?.SaleQty == 0 ? 0 : (today?.SaleQty / (decimal?)yestoday?.SaleQty) - 1) * 100;
                    var neighborhoodCom_SaleCount = (yestoday?.SaleCount == 0 ? 0 : (today?.SaleCount / (decimal?)yestoday?.SaleCount) - 1) * 100;
                    var neighborhoodCom_KpiQty = (yestoday?.KpiQty == 0 ? 0 : (today?.KpiQty / (decimal?)yestoday?.KpiQty) - 1) * 100;
                    var neighborhoodCom_SaleKdj = (yestoday?.SaleKdj == 0 ? 0 : (today?.SaleKdj / (decimal?)yestoday?.SaleKdj) - 1) * 100;
                    var neighborhoodCom_Closing = (yestoday?.Closing == 0 ? 0 : (today?.Closing / (decimal?)yestoday?.Closing) - 1) * 100;
                    var neighborhoodCom_SalelDv = (yestoday?.SalelDv == 0 ? 0 : (today?.SalelDv / (decimal?)yestoday?.SalelDv) - 1) * 100;
                    var neighborhoodCom_Discount = (yestoday?.Discount == 0 ? 0 : (today?.Discount / (decimal?)yestoday?.Discount) - 1) * 100;
                    var neighborhoodCom_SaleJdj = (yestoday?.SaleJdj == 0 ? 0 : (today?.SaleJdj / (decimal?)yestoday?.SaleJdj) - 1) * 100;
                    var weekCom_SaleAmount = (lastweek?.SaleAmount == 0 ? 0 : (today?.SaleAmount / (decimal?)lastweek?.SaleAmount) - 1) * 100;
                    var weekCom_SaleQty = (lastweek?.SaleQty == 0 ? 0 : (today?.SaleQty / (decimal?)lastweek?.SaleQty) - 1) * 100;
                    var weekCom_SaleCount = (lastweek?.SaleCount == 0 ? 0 : (today?.SaleCount / (decimal?)lastweek?.SaleCount) - 1) * 100;
                    var weekCom_KpiQty = (lastweek?.KpiQty == 0 ? 0 : (today?.KpiQty / (decimal?)lastweek?.KpiQty) - 1) * 100;
                    var weekCom_SaleKdj = (lastweek?.SaleKdj == 0 ? 0 : (today?.SaleKdj / (decimal?)lastweek?.SaleKdj) - 1) * 100;
                    var weekCom_Closing = (lastweek?.Closing == 0 ? 0 : (today?.Closing / (decimal?)lastweek?.Closing) - 1) * 100;
                    var weekCom_SalelDv = (lastweek?.SalelDv == 0 ? 0 : (today?.SalelDv / (decimal?)lastweek?.SalelDv) - 1) * 100;
                    var weekCom_Discount = (lastweek?.Discount == 0 ? 0 : (today?.Discount / (decimal?)lastweek?.Discount) - 1) * 100;
                    var weekCom_SaleJdj = (lastweek?.SaleJdj == 0 ? 0 : (today?.SaleJdj / (decimal?)lastweek?.SaleJdj) - 1) * 100;
                    foreach (var it in item)
                    {
                        var datetime = Convert.ToDateTime(it.SaleDate);
                        var str = TimeTrackingReportHandler.ReturnWeek(datetime);
                        it.Week = $"星期{str}";
                        it.NeighborhoodCom_SaleAmount = neighborhoodCom_SaleAmount ?? 0;
                        it.NeighborhoodCom_SaleQty = neighborhoodCom_SaleQty ?? 0;
                        it.NeighborhoodCom_SaleCount = neighborhoodCom_SaleCount ?? 0;
                        it.NeighborhoodCom_KpiQty = neighborhoodCom_KpiQty ?? 0;
                        it.NeighborhoodCom_SaleKdj = neighborhoodCom_SaleKdj ?? 0;
                        it.NeighborhoodCom_Closing = neighborhoodCom_Closing ?? 0;
                        it.NeighborhoodCom_SalelDv = neighborhoodCom_SalelDv ?? 0;
                        it.NeighborhoodCom_Discount = neighborhoodCom_Discount ?? 0;
                        it.NeighborhoodCom_SaleJdj = neighborhoodCom_SaleJdj ?? 0;
                        it.WeekCom_SaleAmount = weekCom_SaleAmount ?? 0;
                        it.WeekCom_SaleQty = weekCom_SaleQty ?? 0;
                        it.WeekCom_SaleCount = weekCom_SaleCount ?? 0;
                        it.WeekCom_KpiQty = weekCom_KpiQty ?? 0;
                        it.WeekCom_SaleKdj = weekCom_SaleKdj ?? 0;
                        it.WeekCom_Closing = weekCom_Closing ?? 0;
                        it.WeekCom_SalelDv = weekCom_SalelDv ?? 0;
                        it.WeekCom_Discount = weekCom_Discount ?? 0;
                        it.WeekCom_SaleJdj = weekCom_SaleJdj ?? 0;
                    }
                }
                var finalGroup = newGroup.Select(item => new TimeAnalysisReportModel.ListModel
                {
                    OperationAreaName = item.Key.OperationAreaName,
                    group = item.GroupBy(d => d.OperationAreaID + "_" + d.SaleDate).ToList()
                }).ToList();
                return finalGroup;
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return null;
            }
        }

        /// <summary>
        /// 获取6个分部
        /// </summary>
        /// <param name="EndDate1"></param>
        /// <param name="EndDate2"></param>
        /// <param name="EndDate3"></param>
        /// <returns></returns>
        public IList<TimeAnalysisReportModel.ListModel> GetReprot2(DateTime EndDate1, DateTime EndDate2, DateTime EndDate3, int ShopType = 0)
        {
            try
            {
                var time1 = $"{EndDate1:yyyy-MM-dd}";
                var time2 = $"{EndDate2:yyyy-MM-dd}";
                var time3 = $"{EndDate3:yyyy-MM-dd}";
                var sql =
                    $@"exec Time_Analysis_Report 'KM','{time1}','{EndDate1:yyyy-MM-dd HH:00:00}','{time2}','{
                            EndDate2
                        :yyyy-MM-dd HH:00:00}','{time3}','{EndDate3:yyyy-MM-dd HH:00:00}',{ShopType}";
                var db = new HKERPEntities();
                var database = db.Database;
                database.CommandTimeout = 0;
                var group = database.SqlQuery<TimeAnalysisReportModel.CoreModel>(sql)
                    .GroupBy(d => new { d.OperationAreaID, d.OperationAreaName });
                var list = group.SelectMany(d => d).Select(d => new TimeAnalysisReportModel.NeceModel
                {
                    CompanyID = d.CompanyID,
                    OperationAreaID = d.OperationAreaID,
                    OperationAreaName = d.OperationAreaName,
                    SaleDate = d.SaleDate,
                    SaleAmount = d.SaleAmount,
                    SaleAmount2 = d.SaleAmount2,
                    SaleAmount3 = d.SaleAmount3,
                    SaleQty = d.SaleQty,
                    SaleQty3 = d.SaleQty3,
                    SaleCount = d.SaleCount,
                    SaleCount2 = d.SaleCount2,
                    SaleCount3 = d.SaleCount3,
                    KpiQty = d.KpiQty,
                    KpiQty2 = d.KpiQty2,
                    SaleKdj = d.SaleKdj,
                    Closing = d.Closing * 100,
                    SalelDv = d.SalelDv,
                    Discount = d.Discount,
                    SaleJdj = d.SaleJdj,
                    ShopCount = d.ShopCount,
                    Price=d.Price,
                }).ToList();
                foreach (var item in group)
                {
                    var date1 = item.FirstOrDefault(d => d.SaleDate == time1);
                    var date2 = item.FirstOrDefault(d => d.SaleDate == time2);
                    var date3 = item.FirstOrDefault(d => d.SaleDate == time3);
                    if (date1 == null)
                    {
                        var newItem = new TimeAnalysisReportModel.NeceModel
                        {
                            OperationAreaID = item.Key.OperationAreaID,
                            OperationAreaName = item.Key.OperationAreaName,
                            SaleDate = time1,
                            Week = $"星期{TimeTrackingReportHandler.ReturnWeek(Convert.ToDateTime(time1))}"
                        };
                        list.Add(newItem);
                    }
                    if (date2 == null)
                    {
                        var newItem = new TimeAnalysisReportModel.NeceModel
                        {
                            OperationAreaID = item.Key.OperationAreaID,
                            OperationAreaName = item.Key.OperationAreaName,
                            SaleDate = time2,
                            Week = $"星期{TimeTrackingReportHandler.ReturnWeek(Convert.ToDateTime(time2))}"
                        };
                        list.Add(newItem);
                    }
                    if (date3 == null)
                    {
                        var newItem = new TimeAnalysisReportModel.NeceModel
                        {
                            OperationAreaID = item.Key.OperationAreaID,
                            OperationAreaName = item.Key.OperationAreaName,
                            SaleDate = time3,
                            Week = $"星期{TimeTrackingReportHandler.ReturnWeek(Convert.ToDateTime(time3))}"
                        };
                        list.Add(newItem);
                    }
                }
                var newList = new List<TimeAnalysisReportModel.NeceModel>();

                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda1_date1 = d => (d.SaleDate == time1 && d.OperationAreaID == "KM00000038");
                newList.Add(TranFrom(list.AsQueryable(), time1, "商品1部", "KM111111111", whereLambda1_date1));
                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda1_date2 = d => (d.SaleDate == time2 && d.OperationAreaID == "KM00000038");
                newList.Add(TranFrom(list.AsQueryable(), time2, "商品1部", "KM111111111", whereLambda1_date2));
                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda1_date3 = d => (d.SaleDate == time3 && d.OperationAreaID == "KM00000038");
                newList.Add(TranFrom(list.AsQueryable(), time3, "商品1部", "KM111111111", whereLambda1_date3));

                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda2_date1 = d => (d.SaleDate == time1 && (d.OperationAreaID == "KM00000040" || d.OperationAreaID== "KM00000042"));
                newList.Add(TranFrom(list.AsQueryable(), time1, "商品2部", "KM222222222", whereLambda2_date1));
                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda2_date2 = d => (d.SaleDate == time2 && (d.OperationAreaID == "KM00000040" || d.OperationAreaID == "KM00000042"));
                newList.Add(TranFrom(list.AsQueryable(), time2, "商品2部", "KM222222222", whereLambda2_date2));
                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda2_date3 = d => (d.SaleDate == time3 && (d.OperationAreaID == "KM00000040" || d.OperationAreaID == "KM00000042"));
                newList.Add(TranFrom(list.AsQueryable(), time3, "商品2部", "KM222222222", whereLambda2_date3));

                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda3_date1 = d => (d.SaleDate == time1 && d.OperationAreaID == "KM00000043");
                newList.Add(TranFrom(list.AsQueryable(), time1, "商品3部", "KM333333333", whereLambda3_date1));
                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda3_date2 = d => (d.SaleDate == time2 && d.OperationAreaID == "KM00000043");
                newList.Add(TranFrom(list.AsQueryable(), time2, "商品3部", "KM333333333", whereLambda3_date2));
                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda3_date3 = d => (d.SaleDate == time3 && d.OperationAreaID == "KM00000043");
                newList.Add(TranFrom(list.AsQueryable(), time3, "商品3部", "KM333333333", whereLambda3_date3));

                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda4_date1 = d => (d.SaleDate == time1 && d.OperationAreaID == "KM00000045");
                newList.Add(TranFrom(list.AsQueryable(), time1, "商品4部", "KM444444444", whereLambda4_date1));
                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda4_date2 = d => (d.SaleDate == time2 && d.OperationAreaID == "KM00000045");
                newList.Add(TranFrom(list.AsQueryable(), time2, "商品4部", "KM444444444", whereLambda4_date2));
                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda4_date3 = d => (d.SaleDate == time3 && d.OperationAreaID == "KM00000045");
                newList.Add(TranFrom(list.AsQueryable(), time3, "商品4部", "KM444444444", whereLambda4_date3));

                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda5_date1 = d => (d.SaleDate == time1 && (d.OperationAreaID == "KM00000041" || d.OperationAreaID == "KM00000046"));
                newList.Add(TranFrom(list.AsQueryable(), time1, "商品5部", "KM555555555", whereLambda5_date1));            
                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda5_date2 = d => (d.SaleDate == time2 && (d.OperationAreaID == "KM00000041" || d.OperationAreaID == "KM00000046"));
                newList.Add(TranFrom(list.AsQueryable(), time2, "商品5部", "KM555555555", whereLambda5_date2));            
                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda5_date3 = d => (d.SaleDate == time3 && (d.OperationAreaID == "KM00000041" || d.OperationAreaID == "KM00000046"));
                newList.Add(TranFrom(list.AsQueryable(), time3, "商品5部", "KM555555555", whereLambda5_date3));

                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda6_date1 = d => (d.SaleDate == time1 && (d.OperationAreaID == "KM00000039" || d.OperationAreaID == "KM00000044"));
                newList.Add(TranFrom(list.AsQueryable(), time1, "商品6部", "KM666666666", whereLambda6_date1));            
                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda6_date2 = d => (d.SaleDate == time2 && (d.OperationAreaID == "KM00000039" || d.OperationAreaID == "KM00000044"));
                newList.Add(TranFrom(list.AsQueryable(), time2, "商品6部", "KM666666666", whereLambda6_date2));            
                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda6_date3 = d => (d.SaleDate == time3 && (d.OperationAreaID == "KM00000039" || d.OperationAreaID == "KM00000044"));
                newList.Add(TranFrom(list.AsQueryable(), time3, "商品6部", "KM666666666", whereLambda6_date3));

                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda9_date1 = d => d.SaleDate == time1;
                newList.Add(TranFrom(list.AsQueryable(), time1, "总计", "KM999999999", whereLambda9_date1));
                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda9_date2 = d => d.SaleDate == time2;
                newList.Add(TranFrom(list.AsQueryable(), time2, "总计", "KM999999999", whereLambda9_date2));
                Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda9_date3 = d => d.SaleDate == time3;
                newList.Add(TranFrom(list.AsQueryable(), time3, "总计", "KM999999999", whereLambda9_date3));
                
                var newGroup = newList.OrderBy(d => d.OperationAreaID).ThenBy(d => d.SaleDate).GroupBy(d => new { d.OperationAreaID, d.OperationAreaName });
                foreach (var item in newGroup)
                {
                    var today = item.FirstOrDefault(d => d.SaleDate == time1);
                    var yestoday = item.FirstOrDefault(d => d.SaleDate == time2);
                    var lastweek = item.FirstOrDefault(d => d.SaleDate == time3);
                    var neighborhoodCom_SaleAmount = (yestoday?.SaleAmount == 0 ? 0 : (today?.SaleAmount / yestoday?.SaleAmount) - 1) * 100;
                    var neighborhoodCom_SaleQty = (yestoday?.SaleQty == 0 ? 0 : (today?.SaleQty / (decimal?)yestoday?.SaleQty) - 1) * 100;
                    var neighborhoodCom_SaleCount = (yestoday?.SaleCount == 0 ? 0 : (today?.SaleCount / (decimal?)yestoday?.SaleCount) - 1) * 100;
                    var neighborhoodCom_KpiQty = (yestoday?.KpiQty == 0 ? 0 : (today?.KpiQty / (decimal?)yestoday?.KpiQty) - 1) * 100;
                    var neighborhoodCom_SaleKdj = (yestoday?.SaleKdj == 0 ? 0 : (today?.SaleKdj / (decimal?)yestoday?.SaleKdj) - 1) * 100;
                    var neighborhoodCom_Closing = (yestoday?.Closing == 0 ? 0 : (today?.Closing / (decimal?)yestoday?.Closing) - 1) * 100;
                    var neighborhoodCom_SalelDv = (yestoday?.SalelDv == 0 ? 0 : (today?.SalelDv / (decimal?)yestoday?.SalelDv) - 1) * 100;
                    var neighborhoodCom_Discount = (yestoday?.Discount == 0 ? 0 : (today?.Discount / (decimal?)yestoday?.Discount) - 1) * 100;
                    var neighborhoodCom_SaleJdj = (yestoday?.SaleJdj == 0 ? 0 : (today?.SaleJdj / (decimal?)yestoday?.SaleJdj) - 1) * 100;
                    var weekCom_SaleAmount = (lastweek?.SaleAmount == 0 ? 0 : (today?.SaleAmount / (decimal?)lastweek?.SaleAmount) - 1) * 100;
                    var weekCom_SaleQty = (lastweek?.SaleQty == 0 ? 0 : (today?.SaleQty / (decimal?)lastweek?.SaleQty) - 1) * 100;
                    var weekCom_SaleCount = (lastweek?.SaleCount == 0 ? 0 : (today?.SaleCount / (decimal?)lastweek?.SaleCount) - 1) * 100;
                    var weekCom_KpiQty = (lastweek?.KpiQty == 0 ? 0 : (today?.KpiQty / (decimal?)lastweek?.KpiQty) - 1) * 100;
                    var weekCom_SaleKdj = (lastweek?.SaleKdj == 0 ? 0 : (today?.SaleKdj / (decimal?)lastweek?.SaleKdj) - 1) * 100;
                    var weekCom_Closing = (lastweek?.Closing == 0 ? 0 : (today?.Closing / (decimal?)lastweek?.Closing) - 1) * 100;
                    var weekCom_SalelDv = (lastweek?.SalelDv == 0 ? 0 : (today?.SalelDv / (decimal?)lastweek?.SalelDv) - 1) * 100;
                    var weekCom_Discount = (lastweek?.Discount == 0 ? 0 : (today?.Discount / (decimal?)lastweek?.Discount) - 1) * 100;
                    var weekCom_SaleJdj = (lastweek?.SaleJdj == 0 ? 0 : (today?.SaleJdj / (decimal?)lastweek?.SaleJdj) - 1) * 100;
                    foreach (var it in item)
                    {
                        var datetime = Convert.ToDateTime(it.SaleDate);
                        var str = TimeTrackingReportHandler.ReturnWeek(datetime);
                        it.Week = $"星期{str}";
                        it.NeighborhoodCom_SaleAmount = neighborhoodCom_SaleAmount ?? 0;
                        it.NeighborhoodCom_SaleQty = neighborhoodCom_SaleQty ?? 0;
                        it.NeighborhoodCom_SaleCount = neighborhoodCom_SaleCount ?? 0;
                        it.NeighborhoodCom_KpiQty = neighborhoodCom_KpiQty ?? 0;
                        it.NeighborhoodCom_SaleKdj = neighborhoodCom_SaleKdj ?? 0;
                        it.NeighborhoodCom_Closing = neighborhoodCom_Closing ?? 0;
                        it.NeighborhoodCom_SalelDv = neighborhoodCom_SalelDv ?? 0;
                        it.NeighborhoodCom_Discount = neighborhoodCom_Discount ?? 0;
                        it.NeighborhoodCom_SaleJdj = neighborhoodCom_SaleJdj ?? 0;
                        it.WeekCom_SaleAmount = weekCom_SaleAmount ?? 0;
                        it.WeekCom_SaleQty = weekCom_SaleQty ?? 0;
                        it.WeekCom_SaleCount = weekCom_SaleCount ?? 0;
                        it.WeekCom_KpiQty = weekCom_KpiQty ?? 0;
                        it.WeekCom_SaleKdj = weekCom_SaleKdj ?? 0;
                        it.WeekCom_Closing = weekCom_Closing ?? 0;
                        it.WeekCom_SalelDv = weekCom_SalelDv ?? 0;
                        it.WeekCom_Discount = weekCom_Discount ?? 0;
                        it.WeekCom_SaleJdj = weekCom_SaleJdj ?? 0;
                    }
                }
                var finalGroup = newGroup.Select(item => new TimeAnalysisReportModel.ListModel
                {
                    OperationAreaName = item.Key.OperationAreaName,
                    group = item.GroupBy(d => d.OperationAreaID + "_" + d.SaleDate).ToList()
                }).ToList();
                return finalGroup;
            }
            catch (Exception ex)
            {
                Log.Error(ex);
                return null;
            }
        }

        public TimeAnalysisReportModel.NeceModel TranFrom(IQueryable<TimeAnalysisReportModel.NeceModel> list,string Time,string OperationAreaName,string OperationAreaID,Expression<Func<TimeAnalysisReportModel.NeceModel, bool>> whereLambda)
        {
            try
            {
                var operationArea_date = list.Where(whereLambda);
                var operationArea_date_item = new TimeAnalysisReportModel.NeceModel
                {
                    OperationAreaName = OperationAreaName,
                    OperationAreaID = OperationAreaID,
                    SaleDate = Time,
                    Week = $"星期{TimeTrackingReportHandler.ReturnWeek(Convert.ToDateTime(Time))}",
                    ShopCount = operationArea_date.Sum(d => d.ShopCount),
                    SaleAmount = operationArea_date.Sum(d => d.SaleAmount),
                    SaleAmount2 = operationArea_date.Sum(d => d.SaleAmount2),
                    SaleAmount3 = operationArea_date.Sum(d => d.SaleAmount3),
                    SaleQty = operationArea_date.Sum(d => d.SaleQty),
                    SaleQty3 = operationArea_date.Sum(d => d.SaleQty3),
                    SaleCount = operationArea_date.Sum(d => d.SaleCount),
                    SaleCount2 = operationArea_date.Sum(d => d.SaleCount2),
                    SaleCount3 = operationArea_date.Sum(d => d.SaleCount3),
                    KpiQty = operationArea_date.Sum(d => d.KpiQty),
                    KpiQty2 = operationArea_date.Sum(d => d.KpiQty2),
                    Price = operationArea_date.Sum(d => d.Price),
                };
                operationArea_date_item.SaleKdj = operationArea_date_item?.SaleCount == 0
                    ? 0
                    : operationArea_date_item?.SaleAmount / (decimal?) operationArea_date_item.SaleCount;
                operationArea_date_item.Closing = operationArea_date_item?.KpiQty2 == 0
                    ? 0
                    : operationArea_date_item?.SaleCount2 * 100 / (decimal?) operationArea_date_item.KpiQty2;
                operationArea_date_item.SalelDv = operationArea_date_item?.SaleCount3 == 0
                    ? 0
                    : operationArea_date_item?.SaleQty3 / (decimal?) operationArea_date_item.SaleCount3;
                operationArea_date_item.Discount = operationArea_date_item?.Price == 0
                    ? 0
                    : operationArea_date_item?.SaleAmount2 / (decimal?) operationArea_date_item.Price;
                operationArea_date_item.SaleJdj = operationArea_date_item?.SaleQty3 == 0
                    ? 0
                    : operationArea_date_item?.SaleAmount3 / (decimal?) operationArea_date_item.SaleQty3;
                return operationArea_date_item;
            }
            catch (Exception ex)
            {
                throw(ex);
            }
        }
    }
}
